create table edu_category
(
	id bigint not null
		constraint pk_edu_category
			primary key,
	name nvarchar(50) not null
		constraint uk_edu_category
			unique,
	total_period int not null,
	day_max_period int not null,
	validate_try_time int not null,
	validate_timeout int not null,
	validate_random_range nvarchar(50) not null,
	remark nvarchar(500)
)
go

exec sp_addextendedproperty 'MS_Description', '培训科目信息', 'SCHEMA', 'dbo', 'TABLE', 'edu_category'
go

exec sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_category', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '科目名称', 'SCHEMA', 'dbo', 'TABLE', 'edu_category', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '培训总学时(秒)', 'SCHEMA', 'dbo', 'TABLE', 'edu_category', 'COLUMN', 'total_period'
go

exec sp_addextendedproperty 'MS_Description', '每日最大培训学时(秒)', 'SCHEMA', 'dbo', 'TABLE', 'edu_category', 'COLUMN', 'day_max_period'
go

exec sp_addextendedproperty 'MS_Description', '验证问题允许尝试最大次数', 'SCHEMA', 'dbo', 'TABLE', 'edu_category', 'COLUMN', 'validate_try_time'
go

exec sp_addextendedproperty 'MS_Description', '验证问题回答超时时间(秒)', 'SCHEMA', 'dbo', 'TABLE', 'edu_category', 'COLUMN', 'validate_timeout'
go

exec sp_addextendedproperty 'MS_Description', '验证问题随机弹出时间范围,格式:开始秒杀-结束秒数,如:900-1200', 'SCHEMA', 'dbo', 'TABLE', 'edu_category', 'COLUMN', 'validate_random_range'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'edu_category', 'COLUMN', 'remark'
go

create table edu_courseware
(
	id bigint not null
		constraint pk_edu_courseware
			primary key,
	name nvarchar(50) not null
		constraint uk_edu_courseware
			unique,
	period int not null,
	edu_category_id bigint not null,
	edu_category_name nvarchar(50) not null,
	course_type nvarchar(50) not null,
	path int default 0 not null,
	operate_user_id bigint,
	operate_user_name nvarchar(100),
	operate_date_time datetime
)
go

exec sp_addextendedproperty 'MS_Description', '课程信息', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware'
go

exec sp_addextendedproperty 'MS_Description', '课程主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '课程名称', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '本章节学时(秒)', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware', 'COLUMN', 'period'
go

exec sp_addextendedproperty 'MS_Description', '科目主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware', 'COLUMN', 'edu_category_id'
go

exec sp_addextendedproperty 'MS_Description', '科目名称', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware', 'COLUMN', 'edu_category_name'
go

exec sp_addextendedproperty 'MS_Description', '课件类型[0-图片, 1-视频]', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware', 'COLUMN', 'course_type'
go

exec sp_addextendedproperty 'MS_Description', '排序号', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware', 'COLUMN', 'path'
go

exec sp_addextendedproperty 'MS_Description', '操作人主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware', 'COLUMN', 'operate_user_id'
go

exec sp_addextendedproperty 'MS_Description', '操作人', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware', 'COLUMN', 'operate_user_name'
go

exec sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware', 'COLUMN', 'operate_date_time'
go

create table edu_courseware_details
(
	id bigint not null
		constraint pk_edu_courseware_details
			primary key,
	courseware_id bigint not null,
	name nvarchar(50) not null,
	url nvarchar(500) not null,
	path int default 0 not null
)
go

exec sp_addextendedproperty 'MS_Description', '课程明细', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware_details'
go

exec sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware_details', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '课程主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware_details', 'COLUMN', 'courseware_id'
go

exec sp_addextendedproperty 'MS_Description', '课件名称', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware_details', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '课件地址', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware_details', 'COLUMN', 'url'
go

exec sp_addextendedproperty 'MS_Description', '排序号', 'SCHEMA', 'dbo', 'TABLE', 'edu_courseware_details', 'COLUMN', 'path'
go

create table edu_plan
(
	id bigint not null
		constraint pk_edu_plan
			primary key,
	name nvarchar(50) not null,
	spell nvarchar(50),
	edu_category_id bigint not null,
	edu_category_name nvarchar(50) not null,
	teacher nvarchar(50),
	train_start_date date not null,
	train_end_date date not null,
	exam_start_date date not null,
	exam_end_date date not null,
	paper_id bigint,
	paper_name nvarchar(50),
	plan_count int not null,
	use_count int not null,
	status tinyint not null,
	remark nvarchar(500),
	operate_user_id bigint,
	operate_user_name nvarchar(100),
	operate_date_time datetime,
	constraint uk_edu_plan
		unique (edu_category_id, name)
)
go

exec sp_addextendedproperty 'MS_Description', '培训计划', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan'
go

exec sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '名称', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '名称简拼', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'spell'
go

exec sp_addextendedproperty 'MS_Description', '科目主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'edu_category_id'
go

exec sp_addextendedproperty 'MS_Description', '科目名称', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'edu_category_name'
go

exec sp_addextendedproperty 'MS_Description', '培训讲师', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'teacher'
go

exec sp_addextendedproperty 'MS_Description', '培训开始日期', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'train_start_date'
go

exec sp_addextendedproperty 'MS_Description', '培训结束日期', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'train_end_date'
go

exec sp_addextendedproperty 'MS_Description', '考试开始日期', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'exam_start_date'
go

exec sp_addextendedproperty 'MS_Description', '考试结束日期', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'exam_end_date'
go

exec sp_addextendedproperty 'MS_Description', '试卷主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'paper_id'
go

exec sp_addextendedproperty 'MS_Description', '试卷名称', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'paper_name'
go

exec sp_addextendedproperty 'MS_Description', '计划人数', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'plan_count'
go

exec sp_addextendedproperty 'MS_Description', '已约人数', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'use_count'
go

exec sp_addextendedproperty 'MS_Description', '状态[1-启用, 0-禁用]', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'status'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'remark'
go

exec sp_addextendedproperty 'MS_Description', '操作人主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'operate_user_id'
go

exec sp_addextendedproperty 'MS_Description', '操作人', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'operate_user_name'
go

exec sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', 'dbo', 'TABLE', 'edu_plan', 'COLUMN', 'operate_date_time'
go

create table edu_question
(
	id bigint not null
		constraint pk_edu_question
			primary key,
	title nvarchar(2000) not null,
	question_category int not null,
	edu_category_id bigint not null,
	edu_category_name nvarchar(50) not null,
	option_a nvarchar(2000) not null,
	option_b nvarchar(2000),
	option_c nvarchar(2000),
	option_d nvarchar(2000),
	option_e nvarchar(2000),
	option_f nvarchar(2000),
	answer nvarchar(50) not null,
	answer_resolve nvarchar(max) not null,
	score int not null,
	status tinyint not null,
	remark nvarchar(500),
	operate_user_id bigint,
	operate_user_name nvarchar(100),
	operate_date_time datetime
)
go

exec sp_addextendedproperty 'MS_Description', '试题库', 'SCHEMA', 'dbo', 'TABLE', 'edu_question'
go

exec sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '试题题干', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'title'
go

exec sp_addextendedproperty 'MS_Description', '试题类型[1-单选题, 2-多选题, 3-判断题]  ', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'question_category'
go

exec sp_addextendedproperty 'MS_Description', '科目主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'edu_category_id'
go

exec sp_addextendedproperty 'MS_Description', '科目名称', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'edu_category_name'
go

exec sp_addextendedproperty 'MS_Description', '选项A', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'option_a'
go

exec sp_addextendedproperty 'MS_Description', '选项B', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'option_b'
go

exec sp_addextendedproperty 'MS_Description', '选项C', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'option_c'
go

exec sp_addextendedproperty 'MS_Description', '选项D', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'option_d'
go

exec sp_addextendedproperty 'MS_Description', '选项E', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'option_e'
go

exec sp_addextendedproperty 'MS_Description', '选项F', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'option_f'
go

exec sp_addextendedproperty 'MS_Description', '答案', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'answer'
go

exec sp_addextendedproperty 'MS_Description', '答案解析', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'answer_resolve'
go

exec sp_addextendedproperty 'MS_Description', '分值', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'score'
go

exec sp_addextendedproperty 'MS_Description', '状态[1-启用, 0-禁用]', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'status'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'remark'
go

exec sp_addextendedproperty 'MS_Description', '操作人主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'operate_user_id'
go

exec sp_addextendedproperty 'MS_Description', '操作人', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'operate_user_name'
go

exec sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', 'dbo', 'TABLE', 'edu_question', 'COLUMN', 'operate_date_time'
go

create table edu_student
(
	id bigint not null
		constraint pk_edu_student
			primary key,
	name nvarchar(50) not null,
	pwd nvarchar(50) not null,
	spell nvarchar(100),
	sex nvarchar(10),
	id_number nvarchar(20) not null,
	phone nvarchar(50) not null,
	email nvarchar(50),
	address nvarchar(200),
	edu_category_id bigint not null,
	edu_category_name nvarchar(50) not null,
	plan_id bigint,
	plan_name nchar(10),
	train_total_period int default 0,
	status tinyint not null,
	remark nvarchar(500),
	operate_user_id bigint,
	operate_user_name nvarchar(100),
	operate_date_time datetime
)
go

exec sp_addextendedproperty 'MS_Description', '学员信息', 'SCHEMA', 'dbo', 'TABLE', 'edu_student'
go

exec sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '学员姓名', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '学员密码', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'pwd'
go

exec sp_addextendedproperty 'MS_Description', '姓名简拼', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'spell'
go

exec sp_addextendedproperty 'MS_Description', '性别', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'sex'
go

exec sp_addextendedproperty 'MS_Description', '身份证号码', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'id_number'
go

exec sp_addextendedproperty 'MS_Description', '手机号码', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'phone'
go

exec sp_addextendedproperty 'MS_Description', '电子邮件', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'email'
go

exec sp_addextendedproperty 'MS_Description', '地址', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'address'
go

exec sp_addextendedproperty 'MS_Description', '科目主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'edu_category_id'
go

exec sp_addextendedproperty 'MS_Description', '科目名称', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'edu_category_name'
go

exec sp_addextendedproperty 'MS_Description', '培训计划主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'plan_id'
go

exec sp_addextendedproperty 'MS_Description', '培训计划名称', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'plan_name'
go

exec sp_addextendedproperty 'MS_Description', '已培训总学时(秒)', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'train_total_period'
go

exec sp_addextendedproperty 'MS_Description', '状态 [0-预报名, 1-报名中, 2-学习中, 3-准备考试, 4-考试中, 5-完成]', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'status'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'remark'
go

exec sp_addextendedproperty 'MS_Description', '操作人主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'operate_user_id'
go

exec sp_addextendedproperty 'MS_Description', '操作人', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'operate_user_name'
go

exec sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', 'dbo', 'TABLE', 'edu_student', 'COLUMN', 'operate_date_time'
go

create table edu_student_period_details
(
	id bigint not null
		constraint pk_edu_student_period_details
			primary key,
	courseware_id bigint,
	courseware_name nvarchar(50),
	student_id bigint not null,
	student_name nvarchar(50) not null,
	study_category nvarchar(50) not null,
	ip nvarchar(50) not null,
	sign_in_date_time datetime not null,
	sign_out_date_time datetime not null,
	period int not null,
	operate_date_time datetime not null,
	operate_date date not null
)
go

exec sp_addextendedproperty 'MS_Description', '学员学时明细', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details'
go

exec sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '课件主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'courseware_id'
go

exec sp_addextendedproperty 'MS_Description', '课件名称', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'courseware_name'
go

exec sp_addextendedproperty 'MS_Description', '学员主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'student_id'
go

exec sp_addextendedproperty 'MS_Description', '学员姓名', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'student_name'
go

exec sp_addextendedproperty 'MS_Description', '学习方式', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'study_category'
go

exec sp_addextendedproperty 'MS_Description', 'IP地址', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'ip'
go

exec sp_addextendedproperty 'MS_Description', '签到时间', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'sign_in_date_time'
go

exec sp_addextendedproperty 'MS_Description', '签退时间', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'sign_out_date_time'
go

exec sp_addextendedproperty 'MS_Description', '学习时长(秒)', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'period'
go

exec sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'operate_date_time'
go

exec sp_addextendedproperty 'MS_Description', '操作日期', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_details', 'COLUMN', 'operate_date'
go

create index idx_student_period_details_student_id
	on edu_student_period_details (student_id)
go

create table edu_student_period_summary
(
	student_id bigint not null,
	courseware_id bigint not null,
	period int not null
)
go

exec sp_addextendedproperty 'MS_Description', '学员学时汇总', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_summary'
go

exec sp_addextendedproperty 'MS_Description', '学员主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_summary', 'COLUMN', 'student_id'
go

exec sp_addextendedproperty 'MS_Description', '课件主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_summary', 'COLUMN', 'courseware_id'
go

exec sp_addextendedproperty 'MS_Description', '学时(秒)', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_period_summary', 'COLUMN', 'period'
go

create index idx_edu_student_period_summary_sid_cid
	on edu_student_period_summary (student_id, courseware_id)
go

create table edu_student_question_favorite
(
	id bigint not null
		constraint pk_edu_student_question_favorite
			primary key,
	student_id bigint not null,
	question_id bigint not null,
	category int not null,
	operate_date_time datetime not null
)
go

exec sp_addextendedproperty 'MS_Description', '学员题库收藏', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_question_favorite'
go

exec sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_question_favorite', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '学员主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_question_favorite', 'COLUMN', 'student_id'
go

exec sp_addextendedproperty 'MS_Description', '题库主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_question_favorite', 'COLUMN', 'question_id'
go

exec sp_addextendedproperty 'MS_Description', '收藏类型[1-手动, 2-错题]', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_question_favorite', 'COLUMN', 'category'
go

exec sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_question_favorite', 'COLUMN', 'operate_date_time'
go

create index idx_edu_student_question_favorite_student_id
	on edu_student_question_favorite (student_id)
go

create table edu_student_train_log
(
	id bigint not null
		constraint pk_edu_student_train_log
			primary key,
	student_id bigint not null,
	student_name nvarchar(50) not null,
	operate_date_time datetime not null,
	operate_date date not null,
	msg nvarchar(500) not null
)
go

exec sp_addextendedproperty 'MS_Description', '学员培训日志', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_train_log'
go

exec sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_train_log', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '学员主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_train_log', 'COLUMN', 'student_id'
go

exec sp_addextendedproperty 'MS_Description', '学员姓名', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_train_log', 'COLUMN', 'student_name'
go

exec sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_train_log', 'COLUMN', 'operate_date_time'
go

exec sp_addextendedproperty 'MS_Description', '操作日期', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_train_log', 'COLUMN', 'operate_date'
go

exec sp_addextendedproperty 'MS_Description', '培训消息', 'SCHEMA', 'dbo', 'TABLE', 'edu_student_train_log', 'COLUMN', 'msg'
go

create index idx_edu_student_operate_log_student_id
	on edu_student_train_log (student_id)
go

create table edu_validate_question
(
	id bigint not null
		constraint pk_edu_validate_question
			primary key,
	name nvarchar(200) not null,
	option_a nvarchar(100) not null,
	option_b nvarchar(100) not null,
	option_c nvarchar(100),
	option_d nvarchar(100),
	answer nvarchar(100) not null
)
go

exec sp_addextendedproperty 'MS_Description', '学习验证问题', 'SCHEMA', 'dbo', 'TABLE', 'edu_validate_question'
go

exec sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', 'dbo', 'TABLE', 'edu_validate_question', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '标题', 'SCHEMA', 'dbo', 'TABLE', 'edu_validate_question', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '选项A', 'SCHEMA', 'dbo', 'TABLE', 'edu_validate_question', 'COLUMN', 'option_a'
go

exec sp_addextendedproperty 'MS_Description', '选项B', 'SCHEMA', 'dbo', 'TABLE', 'edu_validate_question', 'COLUMN', 'option_b'
go

exec sp_addextendedproperty 'MS_Description', '选项C', 'SCHEMA', 'dbo', 'TABLE', 'edu_validate_question', 'COLUMN', 'option_c'
go

exec sp_addextendedproperty 'MS_Description', '选项D', 'SCHEMA', 'dbo', 'TABLE', 'edu_validate_question', 'COLUMN', 'option_d'
go

exec sp_addextendedproperty 'MS_Description', '正确答案', 'SCHEMA', 'dbo', 'TABLE', 'edu_validate_question', 'COLUMN', 'answer'
go

create table exam_paper
(
	id bigint not null
		constraint pk_exam_paper
			primary key,
	name nvarchar(200) not null,
	paper_category int not null,
	auto_radio_count int not null,
	auto_checkbox_count int not null,
	auto_yesno_count int not null,
	total_score int not null,
	total_duration int not null,
	pass_score int not null,
	edu_category_id bigint not null,
	edu_category_name nvarchar(50) not null,
	notice nvarchar(2000),
	start_date_time datetime,
	end_date_time datetime,
	usable_count int not null,
	question_order tinyint not null,
	question_option_order tinyint not null,
	show_mode tinyint not null,
	forbid_enter int not null,
	forbid_submit int not null,
	status tinyint not null,
	remark nvarchar(500),
	operate_user_id bigint,
	operate_user_name nvarchar(100),
	operate_date_time datetime
)
go

exec sp_addextendedproperty 'MS_Description', '试卷信息', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper'
go

exec sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '试卷名称', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '试卷类型[1-普通试卷, 2-随机试卷]  ', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'paper_category'
go

exec sp_addextendedproperty 'MS_Description', '自动生成:单选题数', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'auto_radio_count'
go

exec sp_addextendedproperty 'MS_Description', '自动生成:多选题数', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'auto_checkbox_count'
go

exec sp_addextendedproperty 'MS_Description', '自动生成:判断题数', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'auto_yesno_count'
go

exec sp_addextendedproperty 'MS_Description', '试卷总分', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'total_score'
go

exec sp_addextendedproperty 'MS_Description', '考试时长', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'total_duration'
go

exec sp_addextendedproperty 'MS_Description', '及格分数', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'pass_score'
go

exec sp_addextendedproperty 'MS_Description', '科目主键', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'edu_category_id'
go

exec sp_addextendedproperty 'MS_Description', '科目名称', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'edu_category_name'
go

exec sp_addextendedproperty 'MS_Description', '考试须知', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'notice'
go

exec sp_addextendedproperty 'MS_Description', '开始考试时间', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'start_date_time'
go

exec sp_addextendedproperty 'MS_Description', '结束考试时间', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'end_date_time'
go

exec sp_addextendedproperty 'MS_Description', '可考次数', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'usable_count'
go

exec sp_addextendedproperty 'MS_Description', '试题排序[0-固定, 1-随机]', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'question_order'
go

exec sp_addextendedproperty 'MS_Description', '试题选项排序[0-固定, 1-随机]', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'question_option_order'
go

exec sp_addextendedproperty 'MS_Description', '试卷展示[0-整卷展示, 1-单题展示]', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'show_mode'
go

exec sp_addextendedproperty 'MS_Description', '开考限制;考试开始{}分钟后禁止参加考试', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'forbid_enter'
go

exec sp_addextendedproperty 'MS_Description', '交卷限制;开始考试{}分钟内不允许交卷', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'forbid_submit'
go

exec sp_addextendedproperty 'MS_Description', '状态[1-启用, 0-禁用]', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'status'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'remark'
go

exec sp_addextendedproperty 'MS_Description', '操作人主键', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'operate_user_id'
go

exec sp_addextendedproperty 'MS_Description', '操作人', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'operate_user_name'
go

exec sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper', 'COLUMN', 'operate_date_time'
go

create table exam_paper_details
(
	question_id bigint not null,
	paper_id bigint not null,
	path int not null
)
go

exec sp_addextendedproperty 'MS_Description', '试卷试题明细', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper_details'
go

exec sp_addextendedproperty 'MS_Description', '试题主键', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper_details', 'COLUMN', 'question_id'
go

exec sp_addextendedproperty 'MS_Description', '试卷主键', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper_details', 'COLUMN', 'paper_id'
go

exec sp_addextendedproperty 'MS_Description', '排序号', 'SCHEMA', 'dbo', 'TABLE', 'exam_paper_details', 'COLUMN', 'path'
go

create table exam_record
(
	id bigint not null
		constraint pk_exam_record
			primary key,
	student_id bigint not null,
	student_name nvarchar(50) not null,
	student_id_number nvarchar(20) not null,
	paper_id bigint not null,
	paper_name nvarchar(50) not null,
	edu_category_id bigint not null,
	edu_category_name nvarchar(50) not null,
	exam_score int not null,
	exam_result tinyint not null,
	exam_start_date_time datetime not null,
	exam_end_date_time datetime not null,
	exam_duration int not null,
	status tinyint not null
)
go

exec sp_addextendedproperty 'MS_Description', '考试记录', 'SCHEMA', 'dbo', 'TABLE', 'exam_record'
go

exec sp_addextendedproperty 'MS_Description', '考试记录主键', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '学员主键', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'student_id'
go

exec sp_addextendedproperty 'MS_Description', '学员姓名', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'student_name'
go

exec sp_addextendedproperty 'MS_Description', '学员证件号码', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'student_id_number'
go

exec sp_addextendedproperty 'MS_Description', '试卷主键', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'paper_id'
go

exec sp_addextendedproperty 'MS_Description', '试卷名称', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'paper_name'
go

exec sp_addextendedproperty 'MS_Description', '科目主键', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'edu_category_id'
go

exec sp_addextendedproperty 'MS_Description', '科目名称', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'edu_category_name'
go

exec sp_addextendedproperty 'MS_Description', '学员考试分数', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'exam_score'
go

exec sp_addextendedproperty 'MS_Description', '学员考试结果[0-不及格, 1-及格]', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'exam_result'
go

exec sp_addextendedproperty 'MS_Description', '开考时间', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'exam_start_date_time'
go

exec sp_addextendedproperty 'MS_Description', '交卷时间', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'exam_end_date_time'
go

exec sp_addextendedproperty 'MS_Description', '开始用时(分钟)', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'exam_duration'
go

exec sp_addextendedproperty 'MS_Description', '状态[0-未开始, 1-考试中, 1-已交卷]', 'SCHEMA', 'dbo', 'TABLE', 'exam_record', 'COLUMN', 'status'
go

create index idx_exam_record
	on exam_record (student_id, paper_id)
go

create table exam_record_details
(
	record_id int not null,
	question_id int not null,
	answer nvarchar(50),
	status tinyint not null,
	path int not null
)
go

exec sp_addextendedproperty 'MS_Description', '考试试题明细', 'SCHEMA', 'dbo', 'TABLE', 'exam_record_details'
go

exec sp_addextendedproperty 'MS_Description', '考试记录主键', 'SCHEMA', 'dbo', 'TABLE', 'exam_record_details', 'COLUMN', 'record_id'
go

exec sp_addextendedproperty 'MS_Description', '试题主键', 'SCHEMA', 'dbo', 'TABLE', 'exam_record_details', 'COLUMN', 'question_id'
go

exec sp_addextendedproperty 'MS_Description', '用户答案', 'SCHEMA', 'dbo', 'TABLE', 'exam_record_details', 'COLUMN', 'answer'
go

exec sp_addextendedproperty 'MS_Description', '状态[1-正确, 0-错误]', 'SCHEMA', 'dbo', 'TABLE', 'exam_record_details', 'COLUMN', 'status'
go

exec sp_addextendedproperty 'MS_Description', '排序号', 'SCHEMA', 'dbo', 'TABLE', 'exam_record_details', 'COLUMN', 'path'
go

create index uk_exam_record_details
	on exam_record_details (record_id, question_id)
go

